package com.haima.sage.bigdata.api.common;

import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;

import com.fasterxml.jackson.databind.ObjectMapper;
import com.haima.sage.bigdata.api.entity.storage.Storage;
import com.zaxxer.hikari.HikariDataSource;

public class DbUtils {
    private static final Logger LOG = LogManager.getLogger(DbUtils.class);
    private static final Map<String, HikariDataSource> map = new HashMap<>();

    public static synchronized Connection getConnection(String store) throws SQLException {

        DataSource ds = map.get(store);
        if (ds != null) {
            long l2 = System.currentTimeMillis();
            Connection conn = ds.getConnection();
            long l3 = System.currentTimeMillis();
            if (LOG.isDebugEnabled()) {
                LOG.debug("get conn use time:{}", l3 - l2);
            }
            return conn;
        }
        return null;
    }

    public static void merge(Storage storage, boolean readonly) throws IOException {
        HikariDataSource ds = map.get(storage.getId());
        if (ds != null) {
            // TODO 清理数据源
            Thread thread = new Thread(new CleanHandle(ds));
            thread.setDaemon(true);
            thread.start();
        }
        map.put(storage.getId(), createDataSource(storage, readonly));
    }

    public static void merge(Storage storage) throws IOException {
        merge(storage, true);
    }

    private static HikariDataSource createDataSource(Storage storage, boolean readonly) throws IOException {
        ObjectMapper mapper = new ObjectMapper();
        Map<?, ?> map = mapper.readValue(storage.getData(), Map.class);
        if (map == null || map.size() == 0) {
            throw new IllegalArgumentException("db storage config error");
        }
        HikariDataSource ds = new HikariDataSource();
        ds.setJdbcUrl(map.get("url").toString());
        ds.setUsername(map.get("username").toString());
        ds.setPassword(map.get("password").toString());
        ds.setReadOnly(readonly);
        // 等待连接池分配连接的最大时长（毫秒），超过这个时长还没可用的连接则发生SQLException， 缺省:30秒
        ds.setConnectionTimeout(getLong(map, "connectionTimeout", 30000));
        // 一个连接idle状态的最大时长（毫秒），超时则被释放（retired），缺省:10分钟
        ds.setIdleTimeout(getLong(map, "idleTimeout", 600000));
        // 一个连接的生命时长（毫秒），超时而且没被使用则被释放（retired），缺省:30分钟，
        // 建议设置比数据库超时时长少30秒，参考MySQL wait_timeout参数（
        // show variables like '%timeout%';）
        ds.setMaxLifetime(getLong(map, "maxLifetime", 1800000));
        // 连接池中允许的最大连接数。缺省值：10；
        // 推荐的公式：((core_count * 2) + effective_spindle_count)
        ds.setMaximumPoolSize(getInt(map, "maximumPoolSize", 15));
        return ds;
    }

    private static HikariDataSource createDataSource(Storage storage) throws IOException {
        return createDataSource(storage, true);
    }

    private static Long getLong(Map<?, ?> map, String key, long defaultValue) {
        long connectionTimeout = defaultValue;
        Object obj = map.get(key);
        if (obj != null) {
            if (obj instanceof Number) {
                connectionTimeout = ((Number) obj).longValue();

            } else {
                try {
                    connectionTimeout = Long.parseLong(obj.toString());
                } catch (Exception e) {
                }
            }
        }
        return connectionTimeout;
    }

    private static int getInt(Map<?, ?> map, String key, int defaultValue) {
        int connectionTimeout = defaultValue;
        Object obj = map.get(key);
        if (obj != null) {
            if (obj instanceof Number) {
                connectionTimeout = ((Number) obj).intValue();

            } else {
                try {
                    connectionTimeout = Integer.parseInt(obj.toString());
                } catch (Exception e) {
                }
            }
        }
        return connectionTimeout;
    }

    public static ResultSet getResult(String sql, Connection conn) throws SQLException {
        Statement stat = conn.createStatement();
        ResultSet rs = stat.executeQuery(sql);
        return rs;
    }

    public static Map<String, Object> getRowResult(String sql, ResultSet rs) throws SQLException {
        ResultSetMetaData metaData = rs.getMetaData();
        int columnCount = metaData.getColumnCount();
        if (rs.next()) {
            return getRecord(rs, metaData, columnCount);
        }
        return null;
    }

    public static Map<String, Object> getRowResult(String sql, Connection conn) throws SQLException {
        ResultSet rs = getResult(sql, conn);
        return getRowResult(sql, rs);
    }

    public static Object getColumnResult(String sql, Connection conn) throws SQLException {
        ResultSet rs = getResult(sql, conn);
        return getColumnResult(sql, rs);
    }

    public static Object getColumnResult(String sql, ResultSet rs) throws SQLException {
        if (rs.next()) {
            ResultSetMetaData metaData = rs.getMetaData();
            int type = metaData.getColumnType(1);
            return DbUtils.getDbValue(rs, 1, type);
        }
        return null;
    }

    public static List<Map<String, Object>> getListResult(String sql, Connection conn, int maxBatchSize) throws SQLException {
        ResultSet rs = getResult(sql, conn);
        ResultSetMetaData metaData = rs.getMetaData();
        int columnCount = metaData.getColumnCount();

        List<Map<String, Object>> list = new ArrayList<>();
        while (rs.next()) {
            Map<String, Object> map = getRecord(rs, metaData, columnCount);
            list.add(map);
            if (list.size() >= maxBatchSize) {
                break;
            }
        }
        return list;
    }

    public static Map<String, Object> getRecord(ResultSet rs, ResultSetMetaData metaData, int columnCount) throws SQLException {
        Map<String, Object> map = new HashMap<>();
        for (int i = 1; i <= columnCount; i++) {
            String key = metaData.getColumnLabel(i);
            int type = metaData.getColumnType(i);
            Object obj = getDbValue(rs, i, type);
            map.put(key, obj);
        }
        return map;
    }

    public static Object getDbValue(ResultSet rs, int i, int type) throws SQLException {
        switch (type) {
            case Types.BOOLEAN:
                return rs.getBoolean(i);
            case Types.TIMESTAMP:
                Timestamp t = rs.getTimestamp(i);
                if (t != null) {
                    return new Date(t.getTime());
                }
                break;
            case Types.DATE:
                return rs.getDate(i);
            case Types.TIME:
                return rs.getTime(i);
            case Types.TINYINT:
            case Types.SMALLINT:
            case Types.INTEGER:
            case Types.BIT:
                return rs.getInt(i);
            case Types.BIGINT:
                return rs.getLong(i);
            case Types.FLOAT:
                return rs.getFloat(i);
            case Types.DOUBLE:
            case Types.REAL:
            case Types.NUMERIC:
            case Types.DECIMAL:
                return rs.getDouble(i);
            case Types.CHAR:
            case Types.VARCHAR:
            case Types.LONGVARCHAR:
                return rs.getString(i);
            case Types.BINARY:
                return rs.getInt(i);
            default:
                return rs.getObject(i);

        }
        return null;
    }
}
